package com.logica.mdm.webservices.messages;

/**
 * Constants utility class;
 */
public final class SearchPartyConstants {

	/** Define the Nexibook source Constant. */
	public static final String NEXIBOOK_SRC = "NEXIBOOK";
	
	/** Define the Sigeo source Constant. */
	public static final String SIGEO_SRC = "SIGEO";
	
	/** Define the Hermes source Constant. */
	public static final String HERMES_SRC = "HERMES";
	
	/** Define the CTI source Constant. */
	public static final String CTI_SRC = "CTI";
	
	/**
	 * SQL request :
	 * Final request is :
	 * 
	 *   1 : PARTY_START_PAGINATE (non mandatory)
	 *   	 PARTY_SELECT_QUERY
	 *   	 PARTY_INNER_QUERY_1
	 *   	 WHERE CLAUSE ON PARTY
	 *   	 PARTY_INNER_QUERY_2
	 *   	 PARTY_JOIN_QUERY
	 *		 OTHER WHERE CLAUSE 
	 *		 PARTY_FINAL_FROM_NAME
	 *		 PARTY_ORDER_BY
	 *		 PARTY_END_PAGINATE
	 *
	 *  OR If there is no WHERE clause on C_PARTY :
	 *  
	 *  2 :  PARTY_START_PAGINATE (non mandatory)
	 *  	 PARTY_SELECT_QUERY
	 *   	 PARTY_C_PARTY_FROM
	 *   	 PARTY_JOIN_QUERY
	 *		 OTHER WHERE CLAUSE 
	 *		 PARTY_FINAL_FROM_NAME
	 *		 PARTY_ORDER_BY
	 *		 PARTY_END_PAGINATE
	 */
	
	/** Start of the paginated request (Max result only)*/
	public static final String PARTY_START_PAGINATE = "SELECT * FROM (";
	
	/** Select part Of the SQL request */
	public static final String PARTY_SELECT_QUERY = "SELECT pkg_ws_party_search.rowid_object,"
				+ " pkg_ws_party_search.party_type,"
				+ " pkg_ws_party_search.party_type_disp,"
				+ " pkg_ws_party_search.gender_cd,"
				+ " pkg_ws_party_search.gender_disp,"
				+ " pkg_ws_party_search.legal_form_cd,"
				+ " pkg_ws_party_search.libelle_legal_form,"
				+ " pkg_ws_party_search.name_prefix_cd,"
				+ " pkg_ws_party_search.last_name,"
				+ " pkg_ws_party_search.first_name,"
				+ " pkg_ws_party_search.birthdate,"
				+ " pkg_ws_party_search.unit_number,"
				+ " pkg_ws_party_search.floor_number,"
				+ " pkg_ws_party_search.street_number,"
				+ " pkg_ws_party_search.street_type,"
				+ " pkg_ws_party_search.street_name,"
				+ " pkg_ws_party_search.address_line_1,"
				+ " pkg_ws_party_search.address_line_2,"
				+ " pkg_ws_party_search.address_line_3,"
				+ " pkg_ws_party_search.address_line_5,"
				+ " pkg_ws_party_search.postal_cd,"
				+ " pkg_ws_party_search.city_name,"
				+ " pkg_ws_party_search.country_name,"
				+ " pkg_ws_party_search.country_code,"
				+ " pkg_ws_party_search.hexacle_voie,"
				+ " pkg_ws_party_search.hexacle_localite,"
				+ " pkg_ws_party_search.home_phone_number,"
				+ " pkg_ws_party_search.cell_phone_number,"
				+ " pkg_ws_party_search.work_phone_number,"
				+ " pkg_ws_party_search.fax_phone_number,"
				+ " pkg_ws_party_search.electronic_address,"
//				+ " pkg_ws_party_search.type_contrat,"
//				+ " pkg_ws_party_search.statut_contrat,"
//				+ " pkg_ws_party_search.numero_contrat,"
//				+ " pkg_ws_party_search.contract_office,"
//				+ " pkg_ws_party_search.date_debut_validite_contrat,"
//				+ " pkg_ws_party_search.date_fin_validite_contrat,"
				+ " pkg_ws_party_search.flag_vip,"
				+ " pkg_ws_party_search.flag_partenaire,"
				+ " pkg_ws_party_search.debranchement_sigeo"
//				+ " pkg_ws_party_search.crm_id,"
//				+ " pkg_ws_party_search.logement_id,"
//				+ " pkg_ws_party_search.service_id"
				+ " FROM ( SELECT /*+ LEADING (ic_party) */"
		      			+ " ic_party.rowid_object,"
				      	+ " ic_party.party_type,"
				      	+ " c_lu_party_type.party_type_disp,"
				      	+ " ic_party.gender_cd,"
				      	+ " c_lu_gender.gender_disp,"
				      	+ " ic_party.legal_form_cd,"
				      	+ " c_lu_party_legal_form.libelle_legal_form,"
				      	+ " ic_party.name_prefix_cd,"
				      	+ " ic_party.last_name,"
				      	+ " ic_party.first_name,"
				      	+ " c_person_details.birthdate,"
				      	+ " mv_pkg_ws_party_address_pp.unit_number,"
				      	+ " mv_pkg_ws_party_address_pp.floor_number,"
				      	+ " mv_pkg_ws_party_address_pp.street_number,"
				      	+ " mv_pkg_ws_party_address_pp.street_type,"
				      	+ " mv_pkg_ws_party_address_pp.street_name,"
				      	+ " mv_pkg_ws_party_address_pp.address_line_1,"
				      	+ " mv_pkg_ws_party_address_pp.address_line_2,"
				      	+ " mv_pkg_ws_party_address_pp.address_line_3,"
				      	+ " mv_pkg_ws_party_address_pp.address_line_5,"
				      	+ " mv_pkg_ws_party_address_pp.postal_cd,"
				      	+ " mv_pkg_ws_party_address_pp.city_name,"
				      	+ " mv_pkg_ws_party_address_pp.country_name,"
				      	+ " mv_pkg_ws_party_address_pp.country_code,"
				      	+ " mv_pkg_ws_party_address_pp.hexacle_voie,"
				      	+ " mv_pkg_ws_party_address_pp.hexacle_localite,"
				      	+ " mv_pkg_ws_party_phone.dom AS home_phone_number,"
				      	+ " mv_pkg_ws_party_phone.por AS cell_phone_number,"
				      	+ " mv_pkg_ws_party_phone.bur AS work_phone_number,"
				      	+ " mv_pkg_ws_party_phone.fax AS fax_phone_number,"
				      	+ " pkg_ws_party_e_address_pp.electronic_address,"
//				      	+ " c_contract.type_contrat,"
//				      	+ " c_contract.statut as statut_contrat,"
//				      	+ " c_contract.numero_contrat,"
//				      	+ " c_contract.contract_office,"
//				      	+ " c_contract.date_debut_validite as date_debut_validite_contrat,"
//				      	+ " c_contract.date_fin_validite as date_fin_validite_contrat,"
				      	+ " ic_party.flag_vip,"
				      	+ " ic_party.flag_partenaire,"
				      	+ " ic_party.debranchement_sigeo"
//				      	+ " mv_pkg_ws_party_crm_id.pkey_src_object AS crm_id,"
//				      	+ " mv_pkg_ws_party_logement_id.pkey_src_object AS logement_id,"
//				      	+ " mv_pkg_ws_party_service_id.pkey_src_object AS service_id"
				      	+ " FROM ( SELECT rowid_object,"
				      			+ " party_type,"
								+ " gender_cd,"
								+ " legal_form_cd,"
								+ " name_prefix_cd,"
								+ " last_name,"
								+ " first_name,"
								+ " flag_vip,"
								+ " flag_partenaire,"
								+ " debranchement_sigeo"
								+ " FROM";
	
	/** Inner Query for selecting on C_PARTY the SQL request */
	public static final String PARTY_INNER_QUERY_1 = " ("
			+ " SELECT c_party.rowid_object,"
			+ " c_party.party_type,"
			+ " c_party.gender_cd,"
			+ " c_party.legal_form_cd,"
			+ " c_party.name_prefix_cd,"
			+ " c_party.last_name,"
			+ " c_party.first_name,"
			+ " c_party.flag_vip,"
			+ " c_party.flag_partenaire,"
			+ " c_party.debranchement_sigeo"
			+ " FROM c_party"
			+ " WHERE ";
	
	/** END of Inner Query for selecting on C_PARTY the SQL request */
	public static final String PARTY_INNER_QUERY_2 = " )) ic_party";
	
	/** From clause when not selecting on C_PARTY the SQL request */
	public static final String PARTY_C_PARTY_FROM = " c_party) ic_party";
	
	/** joins of the SQL request */
	public static final String PARTY_JOIN_QUERY = 
		  " LEFT OUTER JOIN c_lu_party_legal_form ON ic_party.legal_form_cd = c_lu_party_legal_form.type_legal_form" 
		+ " LEFT OUTER JOIN c_lu_gender ON ic_party.gender_cd = c_lu_gender.gender_code" 
        + " LEFT OUTER JOIN c_lu_party_type ON ic_party.party_type = c_lu_party_type.party_type"
//        + " LEFT OUTER JOIN mv_pkg_ws_party_crm_id ON ic_party.rowid_object = mv_pkg_ws_party_crm_id .rowid_object"
//        + " LEFT OUTER JOIN mv_pkg_ws_party_logement_id ON ic_party.rowid_object = mv_pkg_ws_party_logement_id.rowid_object"
        + " LEFT OUTER JOIN mv_pkg_ws_party_phone ON ic_party.rowid_object = mv_pkg_ws_party_phone.party_id"
//        + " LEFT OUTER JOIN mv_pkg_ws_party_service_id ON ic_party.rowid_object = mv_pkg_ws_party_service_id.rowid_object"
        + " LEFT OUTER JOIN ( SELECT C_PARTY_ELECT_AD.ELECTRONIC_ADDRESS, C_PARTY_ELECT_AD.PARTY_ID"
        					+ " FROM C_PARTY_ELECT_AD ) pkg_ws_party_e_address_pp" 
				        + " ON ic_party.rowid_object = pkg_ws_party_e_address_pp.party_id"
        + " LEFT OUTER JOIN mv_pkg_ws_party_address_pp ON ic_party.rowid_object = mv_pkg_ws_party_address_pp.party_id"
		+ " LEFT OUTER JOIN c_person_details ON ic_party.rowid_object = c_person_details.party_id";
//      + " LEFT OUTER JOIN c_party_contract_rel ON ic_party.rowid_object = c_party_contract_rel.party_id" 
//		+ " LEFT OUTER JOIN c_contract ON c_party_contract_rel.contract_id = c_contract.rowid_object";  

	/** Naming of the virtual table */
	public static final String PARTY_FINAL_FROM_NAME = " ) pkg_ws_party_search";
   
	/** Order by clause of the request */
	public static final String PARTY_ORDER_BY = " ORDER BY last_name ASC, first_name ASC";
	
	/** END of the paginated request (Max result only)*/
	public static final String PARTY_END_PAGINATE = ") WHERE rownum <= :maxResult ";

}
